package com.infopatent.shldevtools.tools;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * 用于操作Excel的工具类,详情请参考内部方法
 *
 * @author shaohailong
 */
public class ExcelTools {

    private static ExcelTools bean = new ExcelTools();
    private static Logger log = Logger.getLogger(ExcelTools.class);

    /**
     * 这里使用了单例模式,通过该静态方法获得该类的唯一实例
     * @return 
     */
    public static ExcelTools getBean() {
        return bean;
    }
    private FileOutputStream output;
    private WritableWorkbook book;

    /**
     * <pre>
     * 导出excel生成文件 例子: 
     * 
     * String base = "D:\\opt\\log\\", fileName = "student.xls", title= "学生成绩表";
     * String[] titles = new String[]{ "学号", "姓名", "语文", "数学", "排名" };
     * int[] coteWidths = new int[]{ 20, 30, 20, 20, 20 }; 
     * int[] numCote = new int[]{0, 2, 3, 4 };
     * List list = new ArrayList();
     * for (i....) {
     *      list.add(new String[]{ "201000" + i, i + "号学生", String.valueOf(100 - i),String.valueOf(95 - i), String.valueOf(i) }); 
     * }
     * System.out.println(ExcelTools.getBean().reportExcel(base, fileName, list, title, coteWidths, titles, numCote));
     * </pre>
     * @param base 根目录(文件存放的目录)
     * @param fileName 文件名
     * @param fullDatas 要导出的数据(这里必须存放String类型的数组)
     * @param title 文件的标题(第一行),如果不为null则创建标题(第一行),如果为null或者"",那么不创建标题,也不占用表格第一行的空间
     * @param coteWidths 每一列的宽度
     * @param titles 表头标题
     * @param numCote 需要数字格式化的列的索引
     * @return String 返回输出文件的物理路径
     *
     *
     */
    @SuppressWarnings("unchecked")
    public String reportExcel(String base, String fileName, List fullDatas,String title, int[] coteWidths, String[] titles, int[] numCote) {
        try {
            if(!base.endsWith(File.separator)){
                base+=File.separator;
            }
            createFolder(base);//如果文件夹不存在就创建
            String filePath = base + fileName;//输出文件路径
            WritableCellFormat cellFormat = new WritableCellFormat();
            cellFormat.setAlignment(Alignment.CENTRE);//居中显示
            WritableSheet sheet = getExcel(filePath, title, coteWidths, titles);//获得工作簿
            if (null != sheet) {
                for (int i = 0; i < fullDatas.size(); i++) {
                    String[] fullData = (String[]) fullDatas.get(i);
                    if (fullData.length == coteWidths.length) {//确保数据个数一致
                        for (int j = 0; j < fullData.length; j++) {
                            boolean isNumeric = false;//判断该列是否需要数字化格式
                            if (null != numCote && numCote.length > 0) {
                                for (int c = 0; c < numCote.length; c++) {
                                    if (j == numCote[c]) {
                                        isNumeric = true;
                                        break;
                                    }
                                }
                            }
                            if (isNumeric) {
                                Double numeric = Double.parseDouble(fullData[j]);
                                Number number = new Number(j, i + ((title != null && !"".equals(title)) ? 2 : 1), numeric, cellFormat);
                                sheet.addCell(number);//数字格式化
                            } else {
                                Label label = new Label(j, i + ((title != null && !"".equals(title)) ? 2 : 1), fullData[j], cellFormat);
                                sheet.addCell(label);//文本数据
                            }
                        }
                    }
                }
            }
            book.write();
            book.close();
            output.close();
            return filePath;
        } catch (Exception e) {
            log.error("导出Excel异常", e);
        }
        return "";
    }
    
    /**
     * <pre>
     * 导出excel生成文件 
     * 例子: 
     * String base = "D:\\opt\\log\\", fileName = "student.xls", title= "学生成绩表";
     * String[] titles = new String[]{ "学号", "姓名", "语文", "数学", "排名" };
     * int[] coteWidths = new int[]{ 20, 30, 20, 20, 20 }; int[] numCote = new int[]{0, 2, 3, 4 };
     * List list = new ArrayList();
     * for (i....) {
     *     list.add(new String[]{ "201000" + i, i + "号学生", String.valueOf(100 - i), String.valueOf(95 - i), String.valueOf(i) });
     * }
     * System.out.println(ExcelTools.getBean().reportExcel(base, fileName, list, title, coteWidths, titles, numCote));
     * </pre>
     *
     * @param base 根目录(文件存放的目录)
     * @param fileName 文件名
     * @param fullDatas 要导出的数据(这里必须存放String类型的数组)
     * @param title 文件的标题(第一行),如果不为null则创建标题(第一行),如果为null或者"",那么不创建标题,也不占用表格第一行的空间
     * @param coteWidths 每一列的宽度
     * @param titles 表头标题
     * @param numCote 需要数字格式化的列的索引
     * @return String 返回输出文件的物理路径
     *
     *
     */
    @SuppressWarnings("unchecked")
    public String reportExcel(String base, String fileName, List fullDatas,
            String sheetName,String title, int[] coteWidths, String[] titles, int[] numCote) {
        try {
            if(!base.endsWith(File.separator)){
                base+=File.separator;
            }
            createFolder(base);//如果文件夹不存在就创建
            String filePath = base + fileName;//输出文件路径
            WritableCellFormat cellFormat = new WritableCellFormat();
            cellFormat.setAlignment(Alignment.CENTRE);//居中显示
            WritableSheet sheet = getExcel(filePath, title, coteWidths, titles, sheetName);//获得工作簿
            if (null != sheet) {
                for (int i = 0; i < fullDatas.size(); i++) {
                    String[] fullData = (String[]) fullDatas.get(i);
                    if (fullData.length == coteWidths.length) {//确保数据个数一致
                        for (int j = 0; j < fullData.length; j++) {
                            boolean isNumeric = false;//判断该列是否需要数字化格式
                            if (null != numCote && numCote.length > 0) {
                                for (int c = 0; c < numCote.length; c++) {
                                    if (j == numCote[c]) {
                                        isNumeric = true;
                                        break;
                                    }
                                }
                            }
                            if (isNumeric) {
                                Double numeric = Double.parseDouble(fullData[j]);
                                Number number = new Number(j, i + ((title != null && !"".equals(title)) ? 2 : 1), numeric, cellFormat);
                                sheet.addCell(number);//数字格式化
                            } else {
                                Label label = new Label(j, i + ((title != null && !"".equals(title)) ? 2 : 1), fullData[j], cellFormat);
                                sheet.addCell(label);//文本数据
                            }
                        }
                    }
                }
            }
            book.write();
            book.close();
            output.close();
            return filePath;
        } catch (Exception e) {
            log.error("导出Excel异常", e);
        }
        return "";
    }

    /**
     * 生成Excel工作簿的另外一个方法
     * @param filePath Excel文件的路径
     * @param title 标题
     * @param coteWidths 各列宽
     * @param titles 各列标题
     * @return 
     */
    private WritableSheet getExcel(String filePath, String title, int coteWidths[], String[] titles) {
        try {
            if (coteWidths.length != titles.length) {
                return null;
            }
            output = new FileOutputStream(filePath);//文件输出流
            book = Workbook.createWorkbook(output); // 创建文件
            WritableSheet sheet = book.createSheet("sheet", 0); // 创建工作薄
            //标题样式
            WritableFont headFont = new WritableFont(
                    WritableFont.ARIAL, 15, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat headCellFormat = new WritableCellFormat(headFont);
            headCellFormat.setAlignment(Alignment.CENTRE);//居中显示
            //表头样式
            WritableFont titleFont = new WritableFont(
                    WritableFont.ARIAL, 11, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont);
            titleCellFormat.setAlignment(Alignment.CENTRE);
            if (title != null && !"".equals(title)) {
                // 设置标题
                sheet.mergeCells(0, 0, titles.length - 1, 0);//合并单元格
                Label label = new Label(0, 0, title, headCellFormat);
                sheet.addCell(label);//标题
            }
            // 设置表头
            for (int i = 0; i < coteWidths.length; i++) {
                sheet.setColumnView(i, coteWidths[i]); // 设置列的宽度
                Label label1 = new Label(i, (title != null && !"".equals(title)) ? 1 : 0, titles[i], titleCellFormat);
                sheet.addCell(label1);//表头标题
            }
            return sheet;
        } catch (Exception e) {
            log.error("生产excel工作簿异常", e);
        }
        return null;
    }
    
    /**
     * 生成Excel 工作簿
     * @param filePath 生成的Excel 路径
     * @param title 表格标题
     * @param coteWidths 各个列的宽度
     * @param titles 各个列的标题
     * @param sheetName 工作表的名称
     * @return 
     */
    private WritableSheet getExcel(String filePath, String title, int coteWidths[], String[] titles, String sheetName) {
        try {
            if (coteWidths.length != titles.length) {
                return null;
            }
            output = new FileOutputStream(filePath);//文件输出流
            book = Workbook.createWorkbook(output); // 创建文件
            WritableSheet sheet = book.createSheet(sheetName, 0); // 创建工作薄
            //标题样式
            WritableFont headFont = new WritableFont(
                    WritableFont.ARIAL, 15, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat headCellFormat = new WritableCellFormat(headFont);
            headCellFormat.setAlignment(Alignment.CENTRE);//居中显示
            //表头样式
            WritableFont titleFont = new WritableFont(
                    WritableFont.ARIAL, 11, WritableFont.BOLD, false,
                    UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
            WritableCellFormat titleCellFormat = new WritableCellFormat(titleFont);
            titleCellFormat.setAlignment(Alignment.CENTRE);
            if (title != null && !"".equals(title)) {
                // 设置标题
                sheet.mergeCells(0, 0, titles.length - 1, 0);//合并单元格
                Label label = new Label(0, 0, title, headCellFormat);
                sheet.addCell(label);//标题
            }
            // 设置表头
            for (int i = 0; i < coteWidths.length; i++) {
                sheet.setColumnView(i, coteWidths[i]); // 设置列的宽度
                Label label1 = new Label(i, (title != null && !"".equals(title)) ? 1 : 0, titles[i], titleCellFormat);
                sheet.addCell(label1);//表头标题
            }
            return sheet;
        } catch (Exception e) {
            log.error("生产excel工作簿异常", e);
        }
        return null;
    }

    /**
     * 创建目录,很常规的方法,没有什么特别之处
     * @param folderPath 需要创建的目录路径
     */
    public void createFolder(String folderPath) {
        try {
            File folder = new File(folderPath);
            if (!folder.exists()) {
                folder.mkdirs();
            }
        } catch (Exception e) {
            log.error("创建文件夹异常", e);
        }
    }
    
    
     /**

     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行

     * @param file 读取数据的源Excel

     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1

     * @return 读出的Excel中数据的内容

     * @throws FileNotFoundException

     * @throws IOException

     */

    public static String[][] getData(File file, int ignoreRows)

           throws FileNotFoundException, IOException {

       List<String[]> result = new ArrayList<String[]>();

       int rowSize = 0;

       BufferedInputStream in = new BufferedInputStream(new FileInputStream(

              file));

       // 打开HSSFWorkbook

       POIFSFileSystem fs = new POIFSFileSystem(in);

       HSSFWorkbook wb = new HSSFWorkbook(fs);

       HSSFCell cell = null;

       for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {

           HSSFSheet st = wb.getSheetAt(sheetIndex);

           // 第一行为标题，不取

           for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {

              HSSFRow row = st.getRow(rowIndex);

              if (row == null) {

                  continue;

              }

              int tempRowSize = row.getLastCellNum() + 1;

              if (tempRowSize > rowSize) {

                  rowSize = tempRowSize;

              }

              String[] values = new String[rowSize];

              Arrays.fill(values, "");

              boolean hasValue = false;

              for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {

                  String value = "";

                  cell = row.getCell(columnIndex);

                  if (cell != null) {

                     // 注意：一定要设成这个，否则可能会出现乱码

                     cell.setEncoding(HSSFCell.ENCODING_UTF_16);

                     switch (cell.getCellType()) {

                     case HSSFCell.CELL_TYPE_STRING:

                         value = cell.getStringCellValue();

                         break;

                     case HSSFCell.CELL_TYPE_NUMERIC:

                         if (HSSFDateUtil.isCellDateFormatted(cell)) {

                            Date date = cell.getDateCellValue();

                            if (date != null) {

                                value = new SimpleDateFormat("yyyy-MM-dd")

                                       .format(date);

                            } else {

                                value = "";

                            }

                         } else {

                            value = new DecimalFormat("0").format(cell

                                   .getNumericCellValue());

                         }

                         break;

                     case HSSFCell.CELL_TYPE_FORMULA:

                         // 导入时如果为公式生成的数据则无值

                         if (!cell.getStringCellValue().equals("")) {

                            value = cell.getStringCellValue();

                         } else {

                            value = cell.getNumericCellValue() + "";

                         }

                         break;

                     case HSSFCell.CELL_TYPE_BLANK:

                         break;

                     case HSSFCell.CELL_TYPE_ERROR:

                         value = "";

                         break;

                     case HSSFCell.CELL_TYPE_BOOLEAN:

                         value = (cell.getBooleanCellValue() == true ? "Y"

                                : "N");

                         break;

                     default:

                         value = "";

                     }

                  }

                  if (columnIndex == 0 && value.trim().equals("")) {

                     break;

                  }

                  values[columnIndex] = rightTrim(value);

                  hasValue = true;

              }

 

              if (hasValue) {

                  result.add(values);

              }

           }

       }

       in.close();

       String[][] returnArray = new String[result.size()][rowSize];

       for (int i = 0; i < returnArray.length; i++) {

           returnArray[i] = (String[]) result.get(i);

       }

       return returnArray;

    }

   

    /**

     * 去掉字符串右边的空格

     * @param str 要处理的字符串

     * @return 处理后的字符串

     */

     public static String rightTrim(String str) {

       if (str == null) {

           return "";

       }

       int length = str.length();

       for (int i = length - 1; i >= 0; i--) {

           if (str.charAt(i) != 0x20) {

              break;

           }

           length--;

       }

       return str.substring(0, length);

    }
    
    
    

    /**
     * 应用程序示例
     * @param args 
     */
    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        String base = "D:\\opt\\log\\", fileName = "student.xls", title = "学生成绩表";
        String[] titles = new String[]{"学号", "姓名", "语文", "数学", "排名"};
        int[] coteWidths = new int[]{20, 30, 20, 20, 20};
        int[] numCote = new int[]{0, 2, 3, 4};
        List list = new ArrayList();
        for (int i = 1; i <= numCote.length; i++) {
            list.add(new String[]{"201000" + i, i + "号学生", String.valueOf(100 - i), String.valueOf(95 - i), String.valueOf(i)});
        }
        System.out.println(ExcelTools.getBean().reportExcel(base, fileName, list, title, coteWidths, titles, numCote));
    }
    
}
